# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql as pq                # 导入pymysql
from comments.utils import outputInfo
import json
from dotenv import dotenv_values

envs = dotenv_values('../.env')


class CommentsPipeline:
    def process_item(self, item, spider):
        return item


class MySQLPipeline(object):
    def __init__(self):
        self.conn = pq.connect(host='localhost', user=envs['DB_USERNAME'],
                               passwd=envs['DB_PASSWORD'],
                               db=envs['DB_DATABASE'], charset='utf8')
        self.cur = self.conn.cursor()

    def process_item(self, item, spider):
        if item.get('type')=='word':
            sql = "insert into word_clouds(content, isbn, created_at, updated_at) VALUES (%s, %s, NOW(), NOW())"
            self.cur.execute(
                sql, (
                    json.dumps(item["content"]),
                    item["isbn"],
                )
            )
        else:
            spider.count += 1

            sql = "insert into comments(content, score, platform, productId, commentId, time, \
                created_at, updated_at \
                ) VALUES (%s, %s, %s, %s, %s, %s, \
                NOW(), NOW())"
            self.cur.execute(
                sql, (
                    item["content"],
                    item["score"],
                    'jd',
                    item['productId'],
                    item["id"],
                    item["time"],
                )
            )

        self.conn.commit()
        return item

    def close_spider(self, spider):
        self.cur.close()
        self.conn.close()


class CleanPipeline:

    def process_item(self, item, spider):

        return {
            "author": (item["author"] or '')[:200],
            "publisher": (item["publisher"] or '')[:200],
            "name": (item["name"] or '')[:200],
            "price": item["price"] or '',
            "cover": (item["cover"] or '')[:200],
            "id": (item["id"] or '')[:20],
            "digest": (item["digest"] or '')[:5000],
            "translator": (item["translator"] or '')[:200],
            "publish_date": item["publishDate"] or None,  # 转换
            "ISBN": item["ISBN"][:20],
            "comment_count": int(item['CommentCount']),
            "average_score": int(item['AverageScore']),
        }
